package libs;

import java.util.Date;
import java.util.Map;
import java.util.Set;

import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashSet;

/**
 *
 * @author dejvino
 */
public class AttendanceImplDb implements Attendance {

    private Connection conn;

    public AttendanceImplDb() {
        this("jdbc:derby://localhost:1527/attendance", "muni", "muni");
    }

    public AttendanceImplDb(Connection conn) {
        if (conn == null) {
            throw new NullPointerException("conn");
        }
        this.conn = conn;
    }

    public AttendanceImplDb(String url, String user, String password) {
        if (url == null) {
            throw new NullPointerException("url");
        }
        if (user == null) {
            throw new NullPointerException("user");
        }
        if (password == null) {
            throw new NullPointerException("password");
        }
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new IllegalArgumentException("Unable to connect to the database");
        }
    }

    /**
     *          Removes the student's registration in the seminar
     *
     * @param   student
     * @param   seminar
     */
    private void deleteStudentRegistrations(Student student, Seminar seminar) {
        if (student == null) {
            throw new NullPointerException("Student is null");
        }
        if (seminar == null) {
            throw new NullPointerException("Seminar is null");
        }

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement("delete from seminar_registrations" +
                    " where subject_code=? and student=?");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, student.getId());
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
    }

    /**
     *          Returns students presence in the seminar on the specified date
     *
     *          1 - present, 0 - absent, -1 - no record
     * @param   student
     * @param   seminar
     * @param   date
     * @return
     */
    int getPresence(Student student, Seminar seminar, Date date) {
        if (student == null) {
            throw new NullPointerException("Student is null");
        }
        if (seminar == null) {
            throw new NullPointerException("Seminar is null");
        }
        if (date == null) {
            throw new NullPointerException("date is null");
        }
        if (student.getId() == null) {
            throw new IllegalArgumentException("Student has no ID.");
        }

        PreparedStatement ps = null;
        ResultSet rs = null;
        int ret = -1;

        try {
            ps = conn.prepareStatement("select present from presences" +
                    " where subject_code=? and seminar_number=? and student=? and date=?");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, seminar.getNumber());
            ps.setInt(3, student.getId());
            ps.setDate(4, new java.sql.Date(date.getTime()));
            rs = ps.executeQuery();
            if (rs.next()) {
                ret = rs.getInt("present");
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        return ret;
    }

    /**
     *          Sets student's attendance in the seminar on the specified date
     *
     * @param   student
     * @param   seminar
     * @param   date
     * @param   present (1 - present, 0 - absent)
     * @throws  libs.AttendanceException
     */
    private void setPresence(Student student, Seminar seminar, Date date, int present)
            throws AttendanceException {
        if (student == null) {
            throw new NullPointerException("The argument 'student' is null.");
        }
        if (seminar == null) {
            throw new NullPointerException("The argument 'seminar' is null.");
        }
        if (date == null) {
            throw new NullPointerException("The argument 'date' is null.");
        }
        if (student.getId() == null) {
            throw new AttendanceException("Student is not registered in this seminar");
        }

        PreparedStatement ps = null;
        ResultSet rs = null;
        // add presence
        try {
            if (!new SeminarExistence(conn, seminar).exists()) {
                throw new AttendanceException("Seminar does not exist");
            }
            if (!new RegistrationExistence(conn, student, seminar).exists()) {
                throw new AttendanceException("Student is not registered in this seminar");
            }
            if (getPresence(student, seminar, date) >= 0) {
                throw new AttendanceException("student already has some record on this date");
            }
            // add presence
            ps = conn.prepareStatement("insert into presences" +
                    " (subject_code,seminar_number,student,date,present)" +
                    " values (?,?,?,?,?)");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, seminar.getNumber());
            ps.setInt(3, student.getId());
            ps.setDate(4, new java.sql.Date(date.getTime()));
            ps.setInt(5, present);
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
    }

    /**
     *          It sets student's absence today in specified seminar.
     * 
     * @param   student     Student.
     * @param   seminar     Seminar.
     * @throws  AttendanceException if the seminar doesn't exist
     *          or the student is not registered in seminar.
     * @throws  NullPointerException if some of the arguments is null.
     */
    public void absent(Student student, Seminar seminar) throws AttendanceException {
        absent(student, seminar, new Date());
    }

    /**
     *          It sets student's absence today in specified seminar
     *          on specified day.
     *
     * @param   student     Student.
     * @param   seminar     Seminar.
     * @throws  AttendanceException if the seminar doesn't exist
     *          or the student is not registered in seminar.
     * @throws  NullPointerException if some of the arguments is null.
     */
    public void absent(Student student, Seminar seminar, Date date) throws AttendanceException {
        setPresence(student, seminar, date, 0);
    }

    /**
     *          It adds a seminar to the attendance book.
     * 
     * @param   seminar     Seminar
     * @return  TRUE if the seminar has been added,
     *          FALSE if the seminar has already existed.
     * @throws  NullPointerException if the seminar is null.
     */
    public boolean addSeminar(Seminar seminar) {
        // TODO Vyhazuje vyjimku
        if (seminar == null) {
            throw new NullPointerException("The argument 'seminar' is null.");
        }
        PreparedStatement preparedSt = null;
        try {
            if (new SeminarExistence(conn, seminar).exists()) {
                return false;
            }
            // check subject existence
            if (new SubjectExistence(conn, seminar.getSubject()).exists()) {
                // add subject
                preparedSt = conn.prepareStatement("insert into subjects (code,name)" +
                        " values (?,?)");
                preparedSt.setString(1, seminar.getSubject().getCode());
                preparedSt.setString(2, seminar.getSubject().getName());
                preparedSt.executeUpdate();
            }

            // add seminar
            preparedSt = conn.prepareStatement("insert into seminars (number,subject_code)" +
                    " values (?,?)");
            preparedSt.setInt(1, seminar.getNumber());
            preparedSt.setString(2, seminar.getSubject().getCode());
            preparedSt.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } catch (NullPointerException ex) {
            System.out.println("AAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
        }
        finally {
            try {
                if (preparedSt != null) {
                    preparedSt.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return true;
    }

    /**
     *          It adds a student to the seminar.
     *          If the student has been already registered in another seminar,
     *          his/her registration in another seminar will be canceled.
     * 
     * @param   student     Student.
     * @param   seminar     Seminar.
     * @return  TRUE if the student has been added,
     *          FALSE if the student has been already registered in this seminar.
     * @throws  AttendanceException if the seminar does not exist.
     * @throws  NullPointerException if some of the arguments is null.
     * @throws  IllegalArgumentException if the student has ID and is not in database.
     */
    public boolean addStudent(Student student, Seminar seminar) throws AttendanceException {
        if (student == null) {
            throw new NullPointerException("The argument 'student' is null.");
        }
        if (seminar == null) {
            throw new NullPointerException("The argument 'seminar' is null.");
        }
        PreparedStatement preparedSt = null;
        ResultSet rs = null;
        try {
            // check seminar existence
            if (!new SeminarExistence(conn, seminar).exists()) {
                throw new AttendanceException("seminar does not exist");
            }
            // check student existence
            if (student.getId() != null) {
                if (!new StudentExistence(conn, student).exists()) {
                    throw new IllegalArgumentException("The student has ID and is not in database.");
                }
            } else {
                // add student
                conn.setAutoCommit(false);
                preparedSt = conn.prepareStatement("insert into students (name)" +
                        " values (?)");
                preparedSt.setString(1, student.getName());
                preparedSt.executeUpdate();
                rs = conn.createStatement().executeQuery("SELECT MAX(id) AS lastId FROM students");
                rs.next();
                student.setId(rs.getInt("lastId"));
                conn.commit();
                conn.setAutoCommit(true);
            }
            // check student's registration
            if (new RegistrationExistence(conn, student, seminar).exists()) {
                return false;
            }
            // cancel other student registrations in this subject
            deleteStudentRegistrations(student, seminar);
            // new student's registration
            preparedSt = conn.prepareStatement("insert into seminar_registrations" +
                    " (subject_code,seminar_number,student) values (?,?,?)");
            preparedSt.setString(1, seminar.getSubject().getCode());
            preparedSt.setInt(2, seminar.getNumber());
            preparedSt.setInt(3, student.getId());
            preparedSt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (preparedSt != null) {
                    preparedSt.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            preparedSt = null;
        }
        return true;
    }

    /**
     *          It changes attendance on the specified day.
     *          If the student was absent, it changes on present and vica versa.
     *
     * @param   student     Student
     * @param   seminar     Student
     * @param   date        Date.
     * @throws  NullPointerException if some of the arguments is null.
     * @throws  AttendanceException if the seminar does not exist,
     *          the student is not registered in the seminar
     *          or he/she has no presence/absence on the specified day.
     */
    public void changeAttendace(Student student, Seminar seminar, Date date)
            throws AttendanceException {
        if (student == null) {
            throw new NullPointerException("The argument 'student' is null.");
        }
        if (seminar == null) {
            throw new NullPointerException("The argument 'seminar' is null.");
        }
        if (date == null) {
            throw new NullPointerException("The argument 'date' is null.");
        }
        if (student.getId() == null) {
            throw new AttendanceException("Student is not registered in this seminar");
        }
        if (getPresence(student, seminar, date) == -1) {
            throw new AttendanceException("Student has no attendance record on this date");
        }

        PreparedStatement ps = null;
        ResultSet rs = null;
        // change student's attendance record
        try {
            if (!new RegistrationExistence(conn, student, seminar).exists()) {
                throw new AttendanceException("Student is not registered in this seminar");
            }
            if (!new SeminarExistence(conn, seminar).exists()) {
               throw new AttendanceException("Seminar does not exist");
            }
            int present = getPresence(student, seminar, date);
            ps = conn.prepareStatement("update presences set present=?" +
                    " where subject_code=? and seminar_number=? and student=? and date=?");
            ps.setInt(1, (present == 0) ? (1) : (0));
            ps.setString(2, seminar.getSubject().getCode());
            ps.setInt(3, seminar.getNumber());
            ps.setInt(4, student.getId());
            ps.setDate(5, new java.sql.Date(date.getTime()));
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
    }

    private Map<Student, Set<Date>> getPresence(Seminar seminar, int present) {
        if (seminar == null) {
            throw new NullPointerException("seminar is null");
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<Student, Set<Date>> map = new HashMap<Student, Set<Date>>();
        // get seminars
        try {
            ps = conn.prepareStatement("select students.id,students.name,date" +
                    " from presences left join students on student=students.id" +
                    " where subject_code=? and seminar_number=? and present=?");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, seminar.getNumber());
            ps.setInt(3, present);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Date date = rs.getDate("date");
                Student student = new Student(name, id);
                if (!map.containsKey(student)) {
                    map.put(student, new HashSet<Date>());
                }
                map.get(student).add(date);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        return map;
    }

    /**
     *          It returns the absence of the students in the seminars.
     *
     * @param   seminar     Seminar.
     * @return  Students and dates when the students were absent.
     * @throws  NullPointerException if the seminar is null.
     */
    public Map<Student, Set<Date>> getAbsence(Seminar seminar) {
        return getPresence(seminar, 0);
    }

    /**
     *          It returns the presence of the students in the seminars.
     *
     * @param   seminar     Seminar.
     * @return  Students and dates when the students were present.
     * @throws  NullPointerException if the seminar is null.
     */
    public Map<Student, Set<Date>> getPresence(Seminar seminar) {
        return getPresence(seminar, 1);
    }

    /**
     *          It returns all seminar groups.
     * 
     * @return  Seminar groups.
     */
    public Set<Seminar> getSeminars() {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Set<Seminar> set = new HashSet<Seminar>();
        // get seminars
        try {
            ps = conn.prepareStatement("select number,subjects.name as subject_name,subject_code" +
                    " from seminars left join subjects on subject_code=subjects.code");
            rs = ps.executeQuery();
            while (rs.next()) {
                int number = rs.getInt("number");
                String code = rs.getString("subject_code");
                String name = rs.getString("subject_name");

                set.add(new Seminar(new Subject(name, code), number));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        return set;
    }

    /**
     *          It returns seminar groups of the subject.
     *
     * @param   subject Subject
     * @return  Seminar groups.
     * @throws  NullPointerException if the subject is null.
     */
    public Set<Seminar> getSeminars(Subject subject) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Set<Seminar> set = new HashSet<Seminar>();
        // get seminars
        try {
            ps = conn.prepareStatement("select number from seminars where subject_code=?");
            ps.setString(1, subject.getCode());
            rs = ps.executeQuery();
            while (rs.next()) {
                int number = rs.getInt("number");

                set.add(new Seminar(subject, number));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        return set;
    }

    /**
     *          It returns all registered students.
     *
     * @return  All registered students.
     */
    public Set<Student> getStudents() {
        PreparedStatement ps = null;
        PreparedStatement pss = null;
        ResultSet rs = null;
        Set<Student> set = new HashSet<Student>();
        // get students
        try {
            ps = conn.prepareStatement("select id,name from students");
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");

                set.add(new Student(name, id));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        return set;
    }

    /**
     *          It returns students of the seminar group.
     *
     * @param   seminar Seminar.
     * @return  Students of the seminar group.
     * @throws  NullPointerException if the seminar is null.
     */
    public Set<Student> getStudents(Seminar seminar) {
        if (seminar == null) {
            throw new NullPointerException("seminar is null");
        }

        PreparedStatement ps = null;
        ResultSet rs = null;
        Set<Student> set = new HashSet<Student>();
        // get students registered in the seminar
        try {
            ps = conn.prepareStatement("select student,students.name" +
                    " from seminar_registrations left join students on student=students.id" +
                    " where subject_code=? and seminar_number=?");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, seminar.getNumber());
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("student");
                String name = rs.getString("name");

                set.add(new Student(name, id));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        return set;
    }

    /**
     *          It set presence of the student today.
     * 
     * @param   student Student.
     * @param   seminar Seminar.
     * @throws  NullPointerException if some of the arguments is null.
     * @throws  AttendanceException if the seminar does not exist
     *          or the student is not registered in the seminar.     *
     */
    public void present(Student student, Seminar seminar) throws AttendanceException {
        setPresence(student, seminar, new Date(), 1);
    }

    /**
     *          It set presence of the student on specified day.
     *
     * @param   student Student.
     * @param   seminar Seminar.
     * @throws  NullPointerException if some of the arguments is null.
     * @throws  AttendanceException if the seminar does not exist
     *          or the student is not registered in the seminar.     *
     */
    public void present(Student student, Seminar seminar, Date date) throws AttendanceException {
        setPresence(student, seminar, date, 1);
    }

    /**
     *          It removes the seminar.
     *
     * @param   seminar Seminar
     * @return  TRUE if the seminar existed, otherwise FALSE.
     * @throws  NullPointerException if the seminar is null.
     */
    public boolean removeSeminar(Seminar seminar) {
        if (seminar == null) {
            throw new NullPointerException("The argument 'seminar' is null.");
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        int ret = 0;
        // remove seminar presences
        try {
            ps = conn.prepareStatement("delete from presences" +
                    " where subject_code=? and seminar_number=?");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, seminar.getNumber());
            ret = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        // remove seminar registrations
        try {
            ps = conn.prepareStatement("delete from seminar_registrations" +
                    " where subject_code=? and seminar_number=?");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, seminar.getNumber());
            ret = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        // remove seminar
        try {
            ps = conn.prepareStatement("delete from seminars" +
                    " where subject_code=? and number=?");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, seminar.getNumber());
            ret = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        return (ret == 1);
    }

    /**
     *          It removes student from the seminar
     *
     * @param   student Student.
     * @param   seminar Seminar.
     * @return  TRUE if the student was registered in seminar,
     *          otherwise FALSE.
     * @throws  NullPointerException if some of the arguments is null
     *          or the student's id is null
     * @throws  AttendanceException if the student has no id.
     */
    public boolean removeStudent(Student student, Seminar seminar) {
        if (seminar == null) {
            throw new NullPointerException("The argument 'seminar' is null.");
        }
        if (student == null) {
            throw new NullPointerException("The argument 'student' is null.");
        }
        if (student.getId() == null) {
            return false;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        int ret = 0;
        // remove student presences
        try {
            ps = conn.prepareStatement("delete from presences" +
                    " where subject_code=? and seminar_number=? and student=?");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, seminar.getNumber());
            ps.setInt(3, student.getId());
            ret = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        // remove student from seminar
        try {
            ps = conn.prepareStatement("delete from seminar_registrations" +
                    " where subject_code=? and seminar_number=? and student=?");
            ps.setString(1, seminar.getSubject().getCode());
            ps.setInt(2, seminar.getNumber());
            ps.setInt(3, student.getId());
            ret = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        return (ret == 1);
    }

    /**
     *          It removes student from all seminars.
     * 
     * @param   student Student
     * @return  TRUE if the student was registered in some seminar,
     *          otherwise FALSE.
     */
    public boolean removeStudent(Student student) {
        if (student == null) {
            throw new NullPointerException("The argument 'student' is null.");
        }
        if (student.getId() == null) {
            return false;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        int ret = 0;
        // remove student presences
        try {
            ps = conn.prepareStatement("delete from presences" +
                    " where student=?");
            ps.setInt(1, student.getId());
            ret = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        // remove student from seminars
        try {
            ps = conn.prepareStatement("delete from seminar_registrations" +
                    " where student=?");
            ps.setInt(1, student.getId());
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        // remove student
        try {
            ps = conn.prepareStatement("delete from students where id=?");
            ps.setInt(1, student.getId());
            ret = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AttendanceImplDb.class.getName()).log(Level.SEVERE, null, ex);
            }
            ps = null;
        }
        return (ret == 1);
    }
}

